import pymysql
from pymysql.cursors import DictCursor
from src.db.config import DB_CONFIG
import logging
from passlib.context import CryptContext

# 日志配置（增加详细）
logging.basicConfig(
    level=logging.DEBUG,  # 改为DEBUG级别，打印更详细日志
    format='%(asctime)s - %(name)s - %(levelname)s - %(message)s'
)
logger = logging.getLogger(__name__)

# 密码加密上下文
pwd_context = CryptContext(schemes=["bcrypt"], deprecated="auto")

def get_db_connection():
    """获取数据库连接（添加详细日志）"""
    try:
        logger.debug(f"尝试连接数据库，配置：{DB_CONFIG.get('host')}:{DB_CONFIG.get('port')}, 数据库名：{DB_CONFIG.get('db')}")
        conn = pymysql.connect(**DB_CONFIG)
        logger.debug("数据库连接成功")
        return conn
    except Exception as e:
        logger.error(f"数据库连接失败：{str(e)}", exc_info=True)  # 打印完整错误堆栈
        return None

def register_user(username, password, real_name, contact_info, address, user_role):
    """注册新用户（添加详细日志追踪）"""
    logger.debug(f"开始注册用户，用户名：{username}")  # 打印传入的用户名
    conn = get_db_connection()
    if not conn:
        logger.error("数据库连接失败，无法注册用户")
        return False

    try:
        with conn.cursor() as cursor:
            # 打印执行的SQL和参数（关键排查点）
            check_sql = "SELECT * FROM user WHERE username = %s"
            logger.debug(f"执行查询SQL：{check_sql}，参数：{username}")
            
            # 执行查询并记录结果
            cursor.execute(check_sql, (username,))
            result = cursor.fetchone()
            logger.debug(f"查询结果：{result}（None表示不存在，其他表示存在）")

            if result:
                logger.warning(f"用户名 {username} 已存在，注册失败")
                return False

            # 加密密码并插入新用户
            hashed_password = pwd_context.hash(password)
            insert_sql = """
            INSERT INTO user (username, password, real_name, contact_info, address, user_role)
            VALUES (%s, %s, %s, %s, %s, %s)
            """
            logger.debug(f"执行插入SQL：{insert_sql}，参数：{username}, [加密密码], {real_name}, {contact_info}, {address}, {user_role}")
            
            cursor.execute(insert_sql, (username, hashed_password, real_name, contact_info, address, user_role))
            conn.commit()
            logger.info(f"用户 {username} 注册成功")
            return True
    except Exception as e:
        conn.rollback()
        logger.error(f"注册过程出错：{str(e)}", exc_info=True)  # 打印完整错误堆栈
        return False
    finally:
        if conn:
            conn.close()
            logger.debug("数据库连接已关闭")

def get_user_by_username(username):
    conn = get_db_connection()
    if not conn:
        return None

    try:
        with conn.cursor(DictCursor) as cursor:
            cursor.execute("SELECT * FROM user WHERE username = %s", (username,))
            return cursor.fetchone()
    except Exception as e:
        logger.error(f"查询用户失败：{str(e)}")
        return None
    finally:
        conn.close()

def authenticate_user(username, password):
    user = get_user_by_username(username)
    if not user:
        logger.warning(f"用户 {username} 不存在")
        return False
    
    is_valid = pwd_context.verify(password, user["password"])
    if not is_valid:
        logger.warning(f"用户 {username} 密码错误")
    return is_valid

def create_artwork(title, description, price, creator, owner_id):
    conn = get_db_connection()
    if not conn:
        return None

    try:
        with conn.cursor() as cursor:
            sql = """
            INSERT INTO artwork 
            (title, description, price, creator, owner_id, create_time)
            VALUES (%s, %s, %s, %s, %s, CURRENT_TIMESTAMP)
            """
            cursor.execute(sql, (title, description, price, creator, owner_id))
            conn.commit()
            return cursor.lastrowid
    except Exception as e:
        logger.error(f"创建艺术品失败: {str(e)}")
        return None
    finally:
        conn.close()

def get_all_artworks():
    conn = get_db_connection()
    if not conn:
        return []

    try:
        with conn.cursor(DictCursor) as cursor:
            cursor.execute("SELECT * FROM artwork ORDER BY create_time DESC")
            return cursor.fetchall()
    except Exception as e:
        logger.error(f"查询艺术品列表失败: {str(e)}")
        return []
    finally:
        conn.close()

def get_artworks_filtered(category=None, era=None, keyword=None, min_price=None, max_price=None):
    conn = get_db_connection()
    if not conn:
        return []

    try:
        with conn.cursor() as cursor:
            sql = """
            SELECT a.*, u.username AS seller_name 
            FROM artwork a
            LEFT JOIN user u ON a.owner_id = u.user_id
            WHERE 1=1
            """
            params = []

            if keyword:
                sql += " AND (a.title LIKE %s OR a.creator LIKE %s)"
                params.extend([f"%{keyword}%", f"%{keyword}%"])

            if min_price is not None:
                sql += " AND a.price >= %s"
                params.append(min_price)
            if max_price is not None:
                sql += " AND a.price <= %s"
                params.append(max_price)

            sql += " ORDER BY a.create_time DESC"
            cursor.execute(sql, params)
            return cursor.fetchall()
    except Exception as e:
        logger.error(f"筛选艺术品失败: {str(e)}")
        return []
    finally:
        conn.close()

def get_artwork_by_id(artwork_id):
    conn = get_db_connection()
    if not conn:
        return None

    try:
        with conn.cursor() as cursor:
            sql = """
            SELECT a.*, u.username AS seller_name, u.real_name AS seller_realname, u.contact_info AS seller_contact
            FROM artwork a
            LEFT JOIN user u ON a.owner_id = u.user_id
            WHERE a.artwork_id = %s
            """
            cursor.execute(sql, (artwork_id,))
            return cursor.fetchone()
    except Exception as e:
        logger.error(f"查询艺术品详情失败: {str(e)}")
        return None
    finally:
        conn.close()

def add_collection(user_id, artwork_id):
    conn = get_db_connection()
    if not conn:
        return False, "数据库连接失败"

    try:
        with conn.cursor() as cursor:
            cursor.execute("SELECT * FROM collection WHERE user_id = %s AND artwork_id = %s", (user_id, artwork_id))
            if cursor.fetchone():
                return False, "已收藏该艺术品"

            sql = "INSERT INTO collection (user_id, artwork_id) VALUES (%s, %s)"
            cursor.execute(sql, (user_id, artwork_id))
            conn.commit()
            return True, "收藏成功"
    except Exception as e:
        conn.rollback()
        logger.error(f"添加收藏失败: {str(e)}")
        return False, "收藏失败"
    finally:
        conn.close()

def remove_collection(user_id, artwork_id):
    conn = get_db_connection()
    if not conn:
        return False, "数据库连接失败"

    try:
        with conn.cursor() as cursor:
            cursor.execute("SELECT * FROM collection WHERE user_id = %s AND artwork_id = %s", (user_id, artwork_id))
            if not cursor.fetchone():
                return False, "未收藏该艺术品"

            sql = "DELETE FROM collection WHERE user_id = %s AND artwork_id = %s"
            cursor.execute(sql, (user_id, artwork_id))
            conn.commit()
            return True, "取消收藏成功"
    except Exception as e:
        conn.rollback()
        logger.error(f"取消收藏失败: {str(e)}")
        return False, "取消收藏失败"
    finally:
        conn.close()

def get_user_collections(user_id):
    conn = get_db_connection()
    if not conn:
        return []

    try:
        with conn.cursor() as cursor:
            sql = """
            SELECT c.collect_time, a.*, u.username AS seller_name
            FROM collection c
            LEFT JOIN artwork a ON c.artwork_id = a.artwork_id
            LEFT JOIN user u ON a.owner_id = u.user_id
            WHERE c.user_id = %s
            ORDER BY c.collect_time DESC
            """
            cursor.execute(sql, (user_id,))
            return cursor.fetchall()
    except Exception as e:
        logger.error(f"查询用户收藏失败: {str(e)}")
        return []
    finally:
        conn.close()

def is_artwork_collected(user_id, artwork_id):
    conn = get_db_connection()
    if not conn:
        return False

    try:
        with conn.cursor() as cursor:
            cursor.execute("SELECT * FROM collection WHERE user_id = %s AND artwork_id = %s", (user_id, artwork_id))
            return cursor.fetchone() is not None
    except Exception as e:
        logger.error(f"判断收藏状态失败: {str(e)}")
        return False
    finally:
        conn.close()

def create_transaction(buyer_id, seller_id, artwork_id, transaction_price, transaction_location=None):
    conn = get_db_connection()
    if not conn:
        return None, "数据库连接失败"

    try:
        with conn.cursor() as cursor:
            cursor.execute("SELECT * FROM artwork WHERE artwork_id = %s AND owner_id = %s", (artwork_id, seller_id))
            if not cursor.fetchone():
                return None, "艺术品不存在或卖家不匹配"

            sql = """
            INSERT INTO transaction 
            (buyer_id, seller_id, artwork_id, transaction_price, transaction_location)
            VALUES (%s, %s, %s, %s, %s)
            """
            cursor.execute(sql, (buyer_id, seller_id, artwork_id, transaction_price, transaction_location))
            conn.commit()
            return cursor.lastrowid, "订单创建成功"
    except Exception as e:
        conn.rollback()
        logger.error(f"创建订单失败: {str(e)}")
        return None, "订单创建失败"
    finally:
        conn.close()

def update_transaction_status(transaction_id, status):
    valid_status = ["待付款", "待收货", "交易完成", "已取消"]
    if status not in valid_status:
        return False, "无效的交易状态"

    conn = get_db_connection()
    if not conn:
        return False, "数据库连接失败"

    try:
        with conn.cursor() as cursor:
            cursor.execute("SELECT * FROM transaction WHERE transaction_id = %s", (transaction_id,))
            if not cursor.fetchone():
                return False, "交易不存在"

            sql = "UPDATE transaction SET transaction_status = %s WHERE transaction_id = %s"
            cursor.execute(sql, (status, transaction_id))
            
            if status == "交易完成":
                cursor.execute("SELECT buyer_id, artwork_id FROM transaction WHERE transaction_id = %s", (transaction_id,))
                trans = cursor.fetchone()
                cursor.execute("UPDATE artwork SET owner_id = %s WHERE artwork_id = %s", (trans["buyer_id"], trans["artwork_id"]))
            
            conn.commit()
            return True, f"交易状态已更新为{status}"
    except Exception as e:
        conn.rollback()
        logger.error(f"更新交易状态失败: {str(e)}")
        return False, "状态更新失败"
    finally:
        conn.close()

def get_user_transactions(user_id, status=None):
    conn = get_db_connection()
    if not conn:
        return [], "数据库连接失败"

    try:
        with conn.cursor() as cursor:
            sql = """
            SELECT t.*, a.title AS artwork_title, a.creator AS artwork_creator, u.username AS seller_name
            FROM transaction t
            LEFT JOIN artwork a ON t.artwork_id = a.artwork_id
            LEFT JOIN user u ON t.seller_id = u.user_id
            WHERE t.buyer_id = %s
            """
            params = [user_id]

            if status:
                sql += " AND t.transaction_status = %s"
                params.append(status)

            sql += " ORDER BY t.transaction_time DESC"
            cursor.execute(sql, params)
            return cursor.fetchall(), "查询成功"
    except Exception as e:
        logger.error(f"查询用户交易失败: {str(e)}")
        return [], "查询失败"
    finally:
        conn.close()

def get_transaction_detail(transaction_id):
    conn = get_db_connection()
    if not conn:
        return None, "数据库连接失败"

    try:
        with conn.cursor() as cursor:
            sql = """
            SELECT t.*, a.title AS artwork_title, a.description AS artwork_desc, 
                   a.creator AS artwork_creator, u1.username AS buyer_name, u2.username AS seller_name
            FROM transaction t
            LEFT JOIN artwork a ON t.artwork_id = a.artwork_id
            LEFT JOIN user u1 ON t.buyer_id = u1.user_id
            LEFT JOIN user u2 ON t.seller_id = u2.user_id
            WHERE t.transaction_id = %s
            """
            cursor.execute(sql, (transaction_id,))
            return cursor.fetchone(), "查询成功"
    except Exception as e:
        logger.error(f"查询交易详情失败: {str(e)}")
        return None, "查询失败"
    finally:
        conn.close()

def add_traceability_info(artwork_id, event_type, event_description):
    conn = get_db_connection()
    if not conn:
        return False, "数据库连接失败"

    try:
        with conn.cursor() as cursor:
            sql = """
            INSERT INTO traceability_info (artwork_id, event_type, event_description)
            VALUES (%s, %s, %s)
            """
            cursor.execute(sql, (artwork_id, event_type, event_description))
            conn.commit()
            return True, "溯源记录添加成功"
    except Exception as e:
        conn.rollback()
        logger.error(f"添加溯源记录失败: {str(e)}")
        return False, "添加失败"
    finally:
        conn.close()

def get_artwork_trace(artwork_id):
    conn = get_db_connection()
    if not conn:
        return [], "数据库连接失败"

    try:
        with conn.cursor() as cursor:
            sql = """
            SELECT t.*, 
                   CASE WHEN t.event_type = '交易' THEN CONCAT('买家：', u1.username, '，卖家：', u2.username) 
                        ELSE '' END AS event_extra
            FROM traceability_info t
            LEFT JOIN transaction trans ON t.traceability_id = trans.transaction_id
            LEFT JOIN user u1 ON trans.buyer_id = u1.user_id
            LEFT JOIN user u2 ON trans.seller_id = u2.user_id
            WHERE t.artwork_id = %s
            ORDER BY t.event_time DESC
            """
            cursor.execute(sql, (artwork_id,))
            return cursor.fetchall(), "查询成功"
    except Exception as e:
        logger.error(f"查询溯源记录失败: {str(e)}")
        return [], "查询失败"
    finally:
        conn.close()
